﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace HaiVan
{
    /// <summary>
    /// cai j lien wan den SQL nen viet hoa het vd: BHVHAIVAN de tranh sai xot
    /// </summary>
    public class DangNhap
    {
        //Kiem tra tai khoan
        public int isMember(string id1, string pass1)
        {
            using (SqlConnection con = new SqlConnection())
            {
                int bol = 0;
                con.ConnectionString =
                "Data Source = localhost;" +
                "Database = QLHS;" +
                "Integrated Security=SSPI";
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    Console.WriteLine("Connection Complete !");
                }
                else
                {
                    Console.WriteLine("faild !");
                }
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "select ID,PASS " +
                "from USERS" + " where ID ='"+id1+"' and PASS='"+pass1+"'";
                // Thực thi câu lệnh và xử lý kết quả.
                using (IDataReader reader = com.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        bol=1;
                    }
                }
                //bol=1 tai khoan dung, bol=0 tai khoan sai
                Console.WriteLine("Bol:" + bol);
                con.Close();
                return bol;
            }
        }
    }
    public class Search
    {
        public int Ten(string ten)
        {
            int bol = 0;
            string Connect =
            "Data Source = localhost;" +
            "Database = QLHS;" +
            "Integrated Security=SSPI";
            SqlConnection con = new SqlConnection(Connect);
            con.Open();
            if (con.State == ConnectionState.Open)
            {
                Console.WriteLine("Connection Complete !");
            }
            else
            {
                Console.WriteLine("faild !");
            }
            SqlCommand com = con.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = "select MAHS,HOTEN,DIACHI,NGAYSINH,EMAIL,MALOP from HOCSINH where HOTEN =N'" + ten + "'";
            // Thực thi câu lệnh và xử lý kết quả.
            using (IDataReader reader = com.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("  {0} : {1} : {2} : {3} : {4} : {5}",
                    reader["MAHS"],
                    reader["HOTEN"],
                    reader["DIACHI"],
                    reader["ngaysinh"],
                    reader["EMAIL"],
                    reader["MALOP"]);
                    bol = 1;
                }
            }
            //bol=1 tim thay, bol=0 ko tim thay
            Console.WriteLine("Bol:" + bol);
            con.Close();
            return bol;
        }
        public int Mahs(string mahs)
        {
            using (SqlConnection con = new SqlConnection())
            {
                int bol = 0;
                con.ConnectionString =
                "Data Source = localhost;" +
                "Database = QLHS;" +
                "Integrated Security=SSPI";
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    Console.WriteLine("Connection Complete !");
                }
                else
                {
                    Console.WriteLine("faild !");
                }
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "select MAHS,HOTEN,DIACHI,NGAYSINH,EMAIL,MALOP from HOCSINH where MAHS ='" + mahs + "'";
                // Thực thi câu lệnh và xử lý kết quả.
                using (IDataReader reader = com.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("  {0} : {1} : {2} : {3} : {4} : {5}",
                        reader["MAHS"],
                        reader["HOTEN"],
                        reader["DIACHI"],
                        reader["ngaysinh"],
                        reader["EMAIL"],
                        reader["MALOP"]);
                        bol = 1;
                    }
                }
                //bol=1 tim thay, bol=0 ko tim thay
                Console.WriteLine("Bol:" + bol);
                con.Close();
                return bol;
            }
        }
        public int Lop(string lop)
        {
            int bol = 0;
            string Connect =
            "Data Source = localhost;" +
            "Database = QLHS;" +
            "Integrated Security=SSPI";
            SqlConnection con = new SqlConnection(Connect);
            con.Open();
            if (con.State == ConnectionState.Open)
            {
                Console.WriteLine("Connection Complete !");
            }
            else
            {
                Console.WriteLine("faild !");
            }
            SqlCommand com = con.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = "select MAHS,HOTEN,DIACHI,NGAYSINH,EMAIL,MALOP from HOCSINH where MALOP ='" + lop + "'";
            // Thực thi câu lệnh và xử lý kết quả.
            using (IDataReader reader = com.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("  {0} : {1} : {2} : {3} : {4} : {5}",
                    reader["MAHS"],
                    reader["HOTEN"],
                    reader["DIACHI"],
                    reader["ngaysinh"],
                    reader["EMAIL"],
                    reader["MALOP"]);
                    bol = 1;
                }
            }
            //bol=1 tim thay, bol=0 ko tim thay
            Console.WriteLine("Bol:" + bol);
            con.Close();
            return bol;
        }
        public int Monhoc(string monhoc)
        {
            int bol = 0;
            string ConnectionString =
            "Data Source = localhost;" +
            "Database = QLHS;" +
            "Integrated Security=SSPI";
            SqlConnection con = new SqlConnection(ConnectionString);
            con.Open();
            if (con.State == ConnectionState.Open)
            {
                Console.WriteLine("Connection Complete !");
            }
            else
            {
                Console.WriteLine("faild !");
            }
            DataSet DS = new DataSet();
            SqlCommand com1 = new SqlCommand();
            com1.Connection = con;
            com1.CommandType = CommandType.Text;
            com1.CommandText = "select MAHS,HOTEN,DIACHI,NGAYSINH,EMAIL,MALOP from HOCSINH";
            SqlDataAdapter DT1 = new SqlDataAdapter();
            DT1.SelectCommand = com1;
            DT1.Fill(DS, "HOCSINH");


            SqlCommand com2 = new SqlCommand();
            com2.Connection = con;
            com2.CommandType = CommandType.Text;
            com2.CommandText = "select MAMH,MAHS from BANGDIEM";
            SqlDataAdapter DT2 = new SqlDataAdapter();
            DT2.SelectCommand = com2;
            DT2.Fill(DS, "BANGDIEM");

            SqlCommand com3 = new SqlCommand();
            com3.Connection = con;
            com3.CommandType = CommandType.Text;
            com3.CommandText = "select MAMH from MONHOC where MAMH='" + monhoc + "' or TENMONHOC=N'" + monhoc + "'";

            DataTable tab1 = DS.Tables[0]; //HocSinh
            DataTable tab2 = DS.Tables[1]; //BangDiem

            using (IDataReader reader = com3.ExecuteReader())
            {
                while (reader.Read())
                {
                    string csdl = reader["MAMH"].ToString();
                    // Console.WriteLine("{0}", csdl);
                    for (int i = 0; i < tab2.Rows.Count; i++)
                    {
                        if (csdl == tab2.Rows[i][0].ToString())
                        {
                            for (int j = 0; j < tab1.Rows.Count; j++)
                            {
                                if (tab2.Rows[i][1].ToString() == tab1.Rows[j][0].ToString())
                                {
                                    Console.WriteLine("\n");
                                    //or (int k = 0; k < tab1.Columns.Count; k++)
                                    Console.WriteLine(tab1.Rows[j][0].ToString());
                                    bol = 1;
                                }
                            }
                        }
                    }
                }
            }
            // Thực thi câu lệnh và xử lý kết quả.

            //bol=1 tim thay, bol=0 ko tim thay
            Console.WriteLine("Bol:" + bol);
            con.Close();
            return bol;
        }
        public bool ThemHS(string mahs, string ten, DateTime ngaysinh, string sex, string diachi, string email, string malop)
        {
            bool bol = false;
            try
            {

                string Connect =
                "Data Source = localhost;" +
                "Database = QLHS;" +
                "Integrated Security=SSPI";
                SqlConnection con = new SqlConnection(Connect);
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    Console.WriteLine("Connection Complete !");
                }
                else
                {
                    Console.WriteLine("faild !");
                }
                string command = "insert into HOCSINH(MAHS,HOTEN, NGAYSINH,GIOITINH,DIACHI,EMAIL,MALOP) values(@MAHS, @HOTEN,@NGAYSINH, @GIOITINH,@DIACHI,@EMAIL,@MALOP)";
                SqlCommand com = new SqlCommand(command, con);

                com.Parameters.Add("@MAHS", SqlDbType.Char);
                com.Parameters.Add("@HOTEN", SqlDbType.NChar);
                com.Parameters.Add("@NGAYSINH", SqlDbType.DateTime);
                com.Parameters.Add("@GIOITINH", SqlDbType.NChar);
                com.Parameters.Add("@DIACHI", SqlDbType.NChar);
                com.Parameters.Add("@EMAIL", SqlDbType.Char);
                com.Parameters.Add("@MALOP", SqlDbType.Char);

                com.Parameters[0].Value = mahs;
                com.Parameters[1].Value = ten;
                com.Parameters[2].Value = ngaysinh;
                com.Parameters[3].Value = sex;
                com.Parameters[4].Value = diachi;
                com.Parameters[5].Value = email;
                com.Parameters[6].Value = malop;
                int kq = com.ExecuteNonQuery();
                if (kq > 0)
                {
                    Console.WriteLine("thanh cong");
                    bol = true;
                }
                else
                {
                    Console.WriteLine("that bai");
                }
                com.Connection.Close();
                return bol;
            }
            catch (SqlException se)
            {

            }
            finally { bol = false; }
            return bol;
        }
        public void delete()
        {
            string Connect =
                "Data Source = localhost;" +
                "Database = QLHS;" +
                "Integrated Security=SSPI";
            SqlConnection con = new SqlConnection(Connect);
            con.Open();
            

        }
    }
    class SQLseverProvider
    {
        /// <summary>
        /// cai j lien wan den SQL nen viet hoa het vd: BHVHAIVAN de tranh sai xot
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            string mahs="017";
            string ten = "dcm";
            DateTime ngaysinh =DateTime.Parse( "12 / 8 / 1994");
            string gioitinh = "Nữ";
            string diachi = "kadhf";
            string email = "74dadd";
            string malop = "A12";
            Search DN=new Search();
            bool i = DN.ThemHS(mahs,ten,ngaysinh,gioitinh,diachi,email,malop);
        }
    }
}
